


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Vendor_Contacts') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Vendor_Contacts
GO

create view v_tb_Vendor_Contacts as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [Vendor_ContactId] as [Vendor_ContactId]
	,[VendorFK] as [VendorFK]
	,[Vendor_Contact_TypeFK] as [Vendor_Contact_TypeFK]
	,[Name] as [Name]
	,[UserName] as [UserName]
	,[SecurityRole] as [SecurityRole]
	,[Password] as [Password]
	,[Enabled] as [Enabled]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [Name]), 255) as [FriendlyName]
from
	tb_Vendor_Contacts
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorContact_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorContact_Test
GO


create proc up_VendorContact_Test
	@Vendor_ContactId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [Vendor_ContactId] 
	from [tb_Vendor_Contacts] source
	where [source].[Vendor_ContactId] = @Vendor_ContactId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorContact_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorContact_Select
GO

create proc up_VendorContact_Select
	 @Vendor_ContactId int = null 
	,@VendorFK int = null 
	,@Vendor_Contact_TypeFK int = null 
	,@Name nvarchar(50) = null 
	,@UserName nvarchar(50) = null 
	,@SecurityRole nvarchar(50) = null 
	,@Password varchar(50) = null 
	,@Enabled bit = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_Vendor_Contacts
where
		IsNull([v_tb_Vendor_Contacts].[Vendor_ContactId], -1) = Coalesce(@Vendor_ContactId, [v_tb_Vendor_Contacts].[Vendor_ContactId], -1)
	AND	IsNull([v_tb_Vendor_Contacts].[VendorFK], -1) = Coalesce(@VendorFK, [v_tb_Vendor_Contacts].[VendorFK], -1)
	AND	IsNull([v_tb_Vendor_Contacts].[Vendor_Contact_TypeFK], -1) = Coalesce(@Vendor_Contact_TypeFK, [v_tb_Vendor_Contacts].[Vendor_Contact_TypeFK], -1)
	AND	IsNull([v_tb_Vendor_Contacts].[Name], '') = Coalesce(@Name, [v_tb_Vendor_Contacts].[Name], '')
	AND	IsNull([v_tb_Vendor_Contacts].[UserName], '') = Coalesce(@UserName, [v_tb_Vendor_Contacts].[UserName], '')
	AND	IsNull([v_tb_Vendor_Contacts].[SecurityRole], '') = Coalesce(@SecurityRole, [v_tb_Vendor_Contacts].[SecurityRole], '')
	AND	IsNull([v_tb_Vendor_Contacts].[Password], '') = Coalesce(@Password, [v_tb_Vendor_Contacts].[Password], '')
	AND	IsNull([v_tb_Vendor_Contacts].[Enabled], 0) = Coalesce(@Enabled, [v_tb_Vendor_Contacts].[Enabled], 0)
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorContact_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorContact_Select_Top1
GO

create proc up_VendorContact_Select_Top1
	 @Vendor_ContactId int = null 
	,@VendorFK int = null 
	,@Vendor_Contact_TypeFK int = null 
	,@Name nvarchar(50) = null 
	,@UserName nvarchar(50) = null 
	,@SecurityRole nvarchar(50) = null 
	,@Password varchar(50) = null 
	,@Enabled bit = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_Vendor_Contacts
where
		IsNull([v_tb_Vendor_Contacts].[Vendor_ContactId], -1) = Coalesce(@Vendor_ContactId, [v_tb_Vendor_Contacts].[Vendor_ContactId], -1)
	AND	IsNull([v_tb_Vendor_Contacts].[VendorFK], -1) = Coalesce(@VendorFK, [v_tb_Vendor_Contacts].[VendorFK], -1)
	AND	IsNull([v_tb_Vendor_Contacts].[Vendor_Contact_TypeFK], -1) = Coalesce(@Vendor_Contact_TypeFK, [v_tb_Vendor_Contacts].[Vendor_Contact_TypeFK], -1)
	AND	IsNull([v_tb_Vendor_Contacts].[Name], '') = Coalesce(@Name, [v_tb_Vendor_Contacts].[Name], '')
	AND	IsNull([v_tb_Vendor_Contacts].[UserName], '') = Coalesce(@UserName, [v_tb_Vendor_Contacts].[UserName], '')
	AND	IsNull([v_tb_Vendor_Contacts].[SecurityRole], '') = Coalesce(@SecurityRole, [v_tb_Vendor_Contacts].[SecurityRole], '')
	AND	IsNull([v_tb_Vendor_Contacts].[Password], '') = Coalesce(@Password, [v_tb_Vendor_Contacts].[Password], '')
	AND	IsNull([v_tb_Vendor_Contacts].[Enabled], 0) = Coalesce(@Enabled, [v_tb_Vendor_Contacts].[Enabled], 0)
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorContact_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorContact_Insert
GO

create proc up_VendorContact_Insert
	 @Vendor_ContactId int =null OUTPUT  
	,@VendorFK int  
	,@Vendor_Contact_TypeFK int  
	,@Name nvarchar(50)  
	,@UserName nvarchar(50)  
	,@SecurityRole nvarchar(50)  
	,@Password varchar(50) = null  
	,@Enabled bit  
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Vendor_Contacts 
	(
	
	 [VendorFK] 
	,[Vendor_Contact_TypeFK] 
	,[Name] 
	,[UserName] 
	,[SecurityRole] 
	,[Password] 
	,[Enabled] 
	)
	values
	(
	
	 @VendorFK 
	,@Vendor_Contact_TypeFK 
	,@Name 
	,@UserName 
	,@SecurityRole 
	,@Password 
	,@Enabled 
	)
set @Vendor_ContactId = scope_identity()
select * from v_tb_Vendor_Contacts where Vendor_ContactId = @Vendor_ContactId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorContact_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorContact_Update
GO


create proc up_VendorContact_Update
	 @Vendor_ContactId int  
	,@VendorFK int = null  
	,@Vendor_Contact_TypeFK int = null  
	,@Name nvarchar(50) = null  
	,@UserName nvarchar(50) = null  
	,@SecurityRole nvarchar(50) = null  
	,@Password varchar(50) = null  
	,@Enabled bit = null  
	,@OriginalVendor_ContactId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalVendor_ContactId is null
	set @OriginalVendor_ContactId = @Vendor_ContactId

-- test
declare @Result bit
execute @Result = up_VendorContact_Test @OriginalVendor_ContactId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Vendor_ContactId)
	raiserror(505060, 16, 1, 'VendorContact', @x)
	return
end

-- proceed
update tb_Vendor_Contacts 
set

	 [VendorFK] = IsNull(@VendorFK, [source].[VendorFK])
	,[Vendor_Contact_TypeFK] = IsNull(@Vendor_Contact_TypeFK, [source].[Vendor_Contact_TypeFK])
	,[Name] = IsNull(@Name, [source].[Name])
	,[UserName] = IsNull(@UserName, [source].[UserName])
	,[SecurityRole] = IsNull(@SecurityRole, [source].[SecurityRole])
	,[Password] =  @Password
	,[Enabled] = IsNull(@Enabled, [source].[Enabled])
	
from tb_Vendor_Contacts source
where
	[source].[Vendor_ContactId] = @OriginalVendor_ContactId


select * from v_tb_Vendor_Contacts where Vendor_ContactId = @Vendor_ContactId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_VendorContact_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_VendorContact_Delete
GO


create proc up_VendorContact_Delete
	@Vendor_ContactId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_VendorContact_Test @Vendor_ContactId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Vendor_ContactId)
	raiserror(505060, 16, 1, 'VendorContact', @x)
	return
end

-- proceed
delete from tb_Vendor_Contacts 
where [Vendor_ContactId] = @Vendor_ContactId

GO





--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_VendorContact_UnitTestInitialize') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_VendorContact_UnitTestInitialize
GO

create proc DevOnly_VendorContact_UnitTestInitialize as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Preps the table for testing, including removing constraints
		Note: Can (probably should) be deleted in production environment
	*/

Declare @exe varchar(1000)

-- remove any existing constraints (where this is the foreign table)
Set @exe = 'ALTER TABLE [tb_Vendor_Contacts] DROP CONSTRAINT FK_tb_Vendor_Contacts_tb_Vendors'
Print @exe
Execute(@exe)
Set @exe = 'ALTER TABLE [tb_Vendor_Contacts] DROP CONSTRAINT FK_tb_Vendor_Contacts_tb_Vendor_Contact_Types'
Print @exe
Execute(@exe)

-- remove any existing constraints (where this is the primary table)

--Disable Unique Indexes

GO




--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_VendorContact_UnitTestClear') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_VendorContact_UnitTestClear
GO

create proc DevOnly_VendorContact_UnitTestClear as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Removes all records
	*/

delete from tb_Vendor_Contacts;

GO




--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_VendorContact_UnitTestTestData') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_VendorContact_UnitTestTestData
GO

create proc DevOnly_VendorContact_UnitTestTestData as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: clears the database and adds test data
		Note: Can (probably should) be deleted in production environment
	*/

-- remove existing records

Execute DevOnly_VendorContact_UnitTestClear;

-- insert new records

execute up_VendorContact_Insert
		 @VendorFK = 958527983
		,@Vendor_Contact_TypeFK = 1859223698
		,@Name = 'RBAAAFRGL38AP1AAUNA1AAHA1LAAAHI5AAA2AAA0QAFPAAAJA3'
		,@UserName = 'AAA884WO1AA32G4AA4AWV8A46AVAAA0SACA7018AARAAA7QAA2'
		,@SecurityRole = '1I4CTAA4MAA8AA3P1A8363AA0DXVXA74BJ0ABATBMAYU14T2XA'
		,@Password = 'X68G3UAAAB1758824A6VCDAJ4AAAA72A2TAAAAW420MLAAAA17'
		,@Enabled = 1

execute up_VendorContact_Insert
		 @VendorFK = 300769464
		,@Vendor_Contact_TypeFK = 1370975642
		,@Name = 'A48ABR5VBAMA7AAA1MATAKSC5N52H5A2AA23AO8IKH7QI536S1'
		,@UserName = 'VA367ANEAA2A62UVRA751AADA73A3AAAQJA3R7QBA7AAA83V6A'
		,@SecurityRole = '68E1A6111JG0BANA6AA0AAUOAO6AAAOAAUR74D6L5A3M0L3A2A'
		,@Password = 'PHC5F6AASAQAAAA3YAA4A71DMV6ABPD6AAJ8AAA4JAA6AADA54'
		,@Enabled = 0

execute up_VendorContact_Insert
		 @VendorFK = 1387197997
		,@Vendor_Contact_TypeFK = 377828552
		,@Name = '3AAA7TAAJLAQ7ARA5OAXA80A1JWWAOQ7AX6A618AH17A74ADAJ'
		,@UserName = '4AAA0HY3AALAJ4IAAA5E7OAD53AHA2AA0A0AVE5A645AAA2A7A'
		,@SecurityRole = 'A8A3AA83G2D2OADC887T35A2A0IAAAAU15AAH2AMA7M4M6J07A'
		,@Password = 'AJ2GAUAD1A5AAJ7CLFAA5CA1OA5NAA1A05K8AANNL4AAA43RAA'
		,@Enabled = 0

execute up_VendorContact_Insert
		 @VendorFK = 1383347552
		,@Vendor_Contact_TypeFK = 1913239412
		,@Name = '0BAA40UWABS0APES63A5O3437W3AAI8LUAC68G82148A0AA345'
		,@UserName = '3QA3QAB05NAK4JABIKA771B7H3A88A2AWAR8A4AAVA4W78Q0XA'
		,@SecurityRole = '35JAQ5AK715P6A2AAY4358C2AM56WAAABK0AS8LAHAAMA0PRHA'
		,@Password = 'A1A5Y5A4A165DY0AS353JE2I6HY2AL30N1FYE8U70HAR7AAAEA'
		,@Enabled = 0

execute up_VendorContact_Insert
		 @VendorFK = 758897588
		,@Vendor_Contact_TypeFK = 560824718
		,@Name = 'QAADRCAAK8A5AABA01B58RAA473LQCJ4VAKA4AA3A6AIMAAAMA'
		,@UserName = '65AAAAAP51A4L2MA3HP2APA7AAA7FJ05BCVAV7A1AAMWS3CBA1'
		,@SecurityRole = 'AQA5G0A1A4J7AWAJ6AAVPA4AFS66A5AA4104AAAAAA68AV2A1A'
		,@Password = '0ACC34HP8N6APN2D6AB88800UAAA3R4A03TASKE4C2A4SAATAM'
		,@Enabled = 1



GO





--use PolePosition

-- unit test procs

if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_VendorContact_UnitTestCleanUp') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_VendorContact_UnitTestCleanUp
GO

create proc DevOnly_VendorContact_UnitTestCleanUp as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Clears table and inserts dummy data for testing purposes
		Note: Can (probably should) be deleted in production environment
	*/

Declare @exe varchar(1000)

-- re-add constraint (where this table is the foreign)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Vendor_Contacts_tb_Vendors]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Vendor_Contacts] ADD CONSTRAINT [FK_tb_Vendor_Contacts_tb_Vendors] FOREIGN KEY ([VendorFK]) REFERENCES [tb_Vendors] ([VendorId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Vendor_Contacts] NOCHECK CONSTRAINT [FK_tb_Vendor_Contacts_tb_Vendors]'
Print @exe
Execute(@exe)

Set @exe = 'if not exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[FK_tb_Vendor_Contacts_tb_Vendor_Contact_Types]'') and OBJECTPROPERTY(id, N''IsConstraint'') = 1) ALTER TABLE [tb_Vendor_Contacts] ADD CONSTRAINT [FK_tb_Vendor_Contacts_tb_Vendor_Contact_Types] FOREIGN KEY ([Vendor_Contact_TypeFK]) REFERENCES [tb_Vendor_Contact_Types] ([Vendor_Contact_TypeId]) NOT FOR REPLICATION'
Print @exe
Execute(@exe)

Set @exe = 'ALTER TABLE [tb_Vendor_Contacts] NOCHECK CONSTRAINT [FK_tb_Vendor_Contacts_tb_Vendor_Contact_Types]'
Print @exe
Execute(@exe)

-- re-add constraint (where this table is the primary)

--Reenable Unique Indexes
GO




--use PolePosition

-- unit test procs
if exists (select * from dbo.sysobjects where id = object_id(N'DevOnly_VendorContact_UnitTest') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DevOnly_VendorContact_UnitTest
GO

create proc DevOnly_VendorContact_UnitTest as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Tests stored procedures 
	*/

	
SET NOCOUNT ON	

print 'START UNIT TEST'

declare @tablecount int
declare @viewcount int

declare @x varchar(100)
declare @y varchar(100)

-- test Clear

print 'Start Unit Test'

-- drop constraints

execute DevOnly_VendorContact_UnitTestInitialize

-- bail on error

if (NOT @@error = 0)
	return(1)

-- clear table

execute DevOnly_VendorContact_UnitTestClear

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling table directly

select @tablecount = count(*) from tb_Vendor_Contacts

if (NOT @tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts should contain 0 records after Clear.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Vendor_Contacts

if (NOT @viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 0 records after Clear.')
	-- return(1)
end

print 'Start Test Insert'

-- test insert

execute up_VendorContact_Insert
		 @VendorFK = 974608667
		,@Vendor_Contact_TypeFK = 1810136808
		,@Name = '56ACHV88T66C73X83NRABFAKA7AQ2AA4AS3UIAADA566AAC3AI'
		,@UserName = 'A5I3A3AAAAAAAA1QAV0A8XA1OTB0AA7AV265AALA42AXA0AC5L'
		,@SecurityRole = '4A662A68GE8HAA185AAA63QAST5E5AAB08A0DY5AA7N2A2WAA7'
		,@Password = 'AAAPA7AAOAXTDH4KEAAAF3A5A314A16KA0F28DTAFQ33IAAOMN'
		,@Enabled = 1

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Vendor_Contacts

if (NOT @tablecount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts should contain 1 record after Insert.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Vendor_Contacts

if (NOT @viewcount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 1 record after Insert.')
	-- return(1)
end

print 'Start Test Insert Again'

-- test insert again


execute up_VendorContact_Insert
		 @VendorFK = 1927007353
		,@Vendor_Contact_TypeFK = 950343269
		,@Name = 'I7IR25A37A8NVA63A4TV705AAOC0H82OIFA6S50P22171AAAX5'
		,@UserName = 'AXA386G8AQA1AA30AC1CIAA0YAA50AA6GA14HA7A8A5VAACSUB'
		,@SecurityRole = 'QR4LAN752P4534AK6G6AAA2A6AFA28A4AAAAARMQ0HVAA2F358'
		,@Password = '46AVAA45A0B00AAADAL1AB1UA86B5OHK31276HAEAUA54A0A2A'
		,@Enabled = 1


-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Vendor_Contacts

if (NOT @tablecount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts should contain 2 records after second Insert.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Vendor_Contacts

if (NOT @viewcount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 2 records after second Insert.')
	-- return(1)
end

print 'Start Test Update All'

-- test update

-- fetch the values from the first record, we'll be updating them



declare @Vendor_ContactId_OldValue int


declare @VendorFK_OldValue int


declare @Vendor_Contact_TypeFK_OldValue int


declare @Name_OldValue nvarchar(50)


declare @UserName_OldValue nvarchar(50)


declare @SecurityRole_OldValue nvarchar(50)


declare @Password_OldValue varchar(50)


declare @Enabled_OldValue bit

select top 1
	 @Vendor_ContactId_OldValue = Vendor_ContactId
	,@VendorFK_OldValue = VendorFK
	,@Vendor_Contact_TypeFK_OldValue = Vendor_Contact_TypeFK
	,@Name_OldValue = Name
	,@UserName_OldValue = UserName
	,@SecurityRole_OldValue = SecurityRole
	,@Password_OldValue = Password
	,@Enabled_OldValue = Enabled
from tb_Vendor_Contacts

-- generate random new data for the record

declare @VendorFK_NewValue int
declare @Vendor_Contact_TypeFK_NewValue int
declare @Name_NewValue nvarchar(50)
declare @UserName_NewValue nvarchar(50)
declare @SecurityRole_NewValue nvarchar(50)
declare @Password_NewValue varchar(50)
declare @Enabled_NewValue bit

set @VendorFK_NewValue = 688240705
set @Vendor_Contact_TypeFK_NewValue = 436026232
set @Name_NewValue = 'JWAA55X8LRB7VA5AA2A1A4AOVY2AA03GA7L3072MMHA8GJ5AUA'
set @UserName_NewValue = 'A1Q3I87WAAA4CA4BAKA5AN2N8AAA02BHFAAAAHMUAA0ICDABAA'
set @SecurityRole_NewValue = 'OCAAAA3AAAA3AAAA3374A0ARA5WA4AA7P5DKAAX77A2A404AAX'
set @Password_NewValue = 'A7PQEG5RIAU8I1A875AA021AA7YIDA8244CTA76A8G2I5A2AA2'
if (@Enabled_OldValue = 1)
begin
	set @Enabled_NewValue = 0
end
else
begin
	set @Enabled_NewValue = 1
end

-- be sure that the random new data is different


if (@VendorFK_OldValue = @VendorFK_NewValue)
begin
	set @x = convert(varchar(100), @VendorFK_OldValue)
	set @y = convert(varchar(100), @VendorFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @VendorFK_OldValue should not equal @VendorFK_NewValue; should be random new.')
	-- return(1)
end

if (@Vendor_Contact_TypeFK_OldValue = @Vendor_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Vendor_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Vendor_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Vendor_Contact_TypeFK_OldValue should not equal @Vendor_Contact_TypeFK_NewValue; should be random new.')
	-- return(1)
end

if (@Name_OldValue = @Name_NewValue)
begin
	set @x = convert(varchar(100), @Name_OldValue)
	set @y = convert(varchar(100), @Name_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Name_OldValue should not equal @Name_NewValue; should be random new.')
	-- return(1)
end

if (@UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @UserName_OldValue should not equal @UserName_NewValue; should be random new.')
	-- return(1)
end

if (@SecurityRole_OldValue = @SecurityRole_NewValue)
begin
	set @x = convert(varchar(100), @SecurityRole_OldValue)
	set @y = convert(varchar(100), @SecurityRole_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @SecurityRole_OldValue should not equal @SecurityRole_NewValue; should be random new.')
	-- return(1)
end

if (@Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Password_OldValue should not equal @Password_NewValue; should be random new.')
	-- return(1)
end

if (@Enabled_OldValue = @Enabled_NewValue)
begin
	set @x = convert(varchar(100), @Enabled_OldValue)
	set @y = convert(varchar(100), @Enabled_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Enabled_OldValue should not equal @Enabled_NewValue; should be random new.')
	-- return(1)
end

-- update the record with the updates

execute up_VendorContact_Update
	 @Vendor_ContactId = @Vendor_ContactId_OldValue
	,@VendorFK = @VendorFK_NewValue
	,@Vendor_Contact_TypeFK = @Vendor_Contact_TypeFK_NewValue
	,@Name = @Name_NewValue
	,@UserName = @UserName_NewValue
	,@SecurityRole = @SecurityRole_NewValue
	,@Password = @Password_NewValue
	,@Enabled = @Enabled_NewValue

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Vendor_Contacts

if (NOT @tablecount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts should contain 2 records, unchanged after Update.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Vendor_Contacts

if (NOT @viewcount = 2)
begin
	set @x = convert(varchar(100), 2)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 2 records, unchanged after Update.')
	-- return(1)
end

-- hold the new values as the old values, so we can test the current record (the new) with what we expect (the old)

set @VendorFK_OldValue = @VendorFK_NewValue
set @Vendor_Contact_TypeFK_OldValue = @Vendor_Contact_TypeFK_NewValue
set @Name_OldValue = @Name_NewValue
set @UserName_OldValue = @UserName_NewValue
set @SecurityRole_OldValue = @SecurityRole_NewValue
set @Password_OldValue = @Password_NewValue
set @Enabled_OldValue = @Enabled_NewValue

-- fetch the current record into the new variables

select top 1
	 @VendorFK_NewValue = VendorFK
	,@Vendor_Contact_TypeFK_NewValue = Vendor_Contact_TypeFK
	,@Name_NewValue = Name
	,@UserName_NewValue = UserName
	,@SecurityRole_NewValue = SecurityRole
	,@Password_NewValue = Password
	,@Enabled_NewValue = Enabled
from tb_Vendor_Contacts
where Vendor_ContactId = @Vendor_ContactId_OldValue

-- check values after update


if (NOT @VendorFK_OldValue = @VendorFK_NewValue)
begin
	set @x = convert(varchar(100), @VendorFK_OldValue)
	set @y = convert(varchar(100), @VendorFK_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.VendorFK value should not match original value after Update.')
	-- return(1)
end

if (NOT @Vendor_Contact_TypeFK_OldValue = @Vendor_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Vendor_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Vendor_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.Vendor_Contact_TypeFK value should not match original value after Update.')
	-- return(1)
end

if (NOT @Name_OldValue = @Name_NewValue)
begin
	set @x = convert(varchar(100), @Name_OldValue)
	set @y = convert(varchar(100), @Name_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.Name value should not match original value after Update.')
	-- return(1)
end

if (NOT @UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.UserName value should not match original value after Update.')
	-- return(1)
end

if (NOT @SecurityRole_OldValue = @SecurityRole_NewValue)
begin
	set @x = convert(varchar(100), @SecurityRole_OldValue)
	set @y = convert(varchar(100), @SecurityRole_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.SecurityRole value should not match original value after Update.')
	-- return(1)
end

if (NOT @Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.Password value should not match original value after Update.')
	-- return(1)
end

if (NOT @Enabled_OldValue = @Enabled_NewValue)
begin
	set @x = convert(varchar(100), @Enabled_OldValue)
	set @y = convert(varchar(100), @Enabled_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.Enabled value should not match original value after Update.')
	-- return(1)
end

-- update only some columns (ordinally even columns)

print 'Start Test Update Some'

-- generate random new data to update only a few columns

set @Vendor_Contact_TypeFK_NewValue = 1391392392
set @UserName_NewValue = 'ROSBA3AA8HA1AA2AIAV4100A5IF42AAA4A4A3A7VBLAA5GA7AU'
set @Password_NewValue = '13ADY705875NN5AFX52A5WMIPA633AOWGBUY716N2L84AH16P8'

-- ensure that the random new data is different


if (@Vendor_Contact_TypeFK_OldValue = @Vendor_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Vendor_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Vendor_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Vendor_Contact_TypeFK_OldValue should not equal @Vendor_Contact_TypeFK_NewValue; should be random new.')
	-- return(1)
end

if (@UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @UserName_OldValue should not equal @UserName_NewValue; should be random new.')
	-- return(1)
end

if (@Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'RDM @Password_OldValue should not equal @Password_NewValue; should be random new.')
	-- return(1)
end

-- update the db record with the partial updates

execute up_VendorContact_Update
	 @Vendor_ContactId = @Vendor_ContactId_OldValue
	,@Vendor_Contact_TypeFK = @Vendor_Contact_TypeFK_NewValue
	,@UserName = @UserName_NewValue
	,@Password = @Password_NewValue

-- bail on error

if (NOT @@error = 0)
	return(1)

-- fetch current db record values into the new variable

select top 1
	 @VendorFK_NewValue = VendorFK
	,@Vendor_Contact_TypeFK_NewValue = Vendor_Contact_TypeFK
	,@Name_NewValue = Name
	,@UserName_NewValue = UserName
	,@SecurityRole_NewValue = SecurityRole
	,@Password_NewValue = Password
	,@Enabled_NewValue = Enabled
from tb_Vendor_Contacts
where Vendor_ContactId = @Vendor_ContactId_OldValue

-- test the values match or do not match as expected


if (NOT @VendorFK_OldValue = @VendorFK_NewValue)
begin
	set @x = convert(varchar(100), @VendorFK_OldValue)
	set @y = convert(varchar(100), @VendorFK_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.VendorFK value should be same after not included in Update.')
	-- return(1)
end

if (@Vendor_Contact_TypeFK_OldValue = @Vendor_Contact_TypeFK_NewValue)
begin
	set @x = convert(varchar(100), @Vendor_Contact_TypeFK_OldValue)
	set @y = convert(varchar(100), @Vendor_Contact_TypeFK_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Vendor_Contacts.Vendor_Contact_TypeFK value should be new after Update.')
	-- return(1)
end

if (NOT @Name_OldValue = @Name_NewValue)
begin
	set @x = convert(varchar(100), @Name_OldValue)
	set @y = convert(varchar(100), @Name_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.Name value should be same after not included in Update.')
	-- return(1)
end

if (@UserName_OldValue = @UserName_NewValue)
begin
	set @x = convert(varchar(100), @UserName_OldValue)
	set @y = convert(varchar(100), @UserName_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Vendor_Contacts.UserName value should be new after Update.')
	-- return(1)
end

if (NOT @SecurityRole_OldValue = @SecurityRole_NewValue)
begin
	set @x = convert(varchar(100), @SecurityRole_OldValue)
	set @y = convert(varchar(100), @SecurityRole_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.SecurityRole value should be same after not included in Update.')
	-- return(1)
end

if (@Password_OldValue = @Password_NewValue)
begin
	set @x = convert(varchar(100), @Password_OldValue)
	set @y = convert(varchar(100), @Password_NewValue)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Vendor_Contacts.Password value should be new after Update.')
	-- return(1)
end

if (NOT @Enabled_OldValue = @Enabled_NewValue)
begin
	set @x = convert(varchar(100), @Enabled_OldValue)
	set @y = convert(varchar(100), @Enabled_NewValue)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts.Enabled value should be same after not included in Update.')
	-- return(1)
end

print 'Start Test Delete'

-- test delete

execute up_VendorContact_Delete
	@Vendor_ContactId = @Vendor_ContactId_OldValue

-- bail on error

if (NOT @@error = 0)
	return(1)

select @tablecount = count(*) from tb_Vendor_Contacts

if (NOT @tablecount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts should contain 1 record after Delete.')
	-- return(1)
end

select @viewcount = count(*) from v_tb_Vendor_Contacts

if (NOT @viewcount = 1)
begin
	set @x = convert(varchar(100), 1)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 1 record after Delete.')
	-- return(1)
end

-- test table count calling the table directly

select @tablecount = count(*) from tb_Vendor_Contacts where Vendor_ContactId = @Vendor_ContactId_OldValue

if (NOT @tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts should contain 0 records when selecting Deleted record.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Vendor_Contacts where Vendor_ContactId = @Vendor_ContactId_OldValue

if (NOT @viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 0 records when selecting Deleted record.')
	-- return(1)
end

print 'Start Test TestData Procedure'

-- test test data

execute DevOnly_VendorContact_UnitTestTestData

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Vendor_Contacts

if (@tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'tb_Vendor_Contacts should not contain 0 records after TestData.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Vendor_Contacts

if (@viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreNotEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 0 records after TestData.')
	-- return(1)
end

print 'Start Test Clear Again'

-- test clear again

execute DevOnly_VendorContact_UnitTestClear

-- bail on error

if (NOT @@error = 0)
	return(1)

-- test table count calling the table directly

select @tablecount = count(*) from tb_Vendor_Contacts

if (NOT @tablecount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @tablecount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'tb_Vendor_Contacts should contain 0 records after Clear.')
	-- return(1)
end

-- test table count calling the view

select @viewcount = count(*) from v_tb_Vendor_Contacts

if (NOT @viewcount = 0)
begin
	set @x = convert(varchar(100), 0)
	set @y = convert(varchar(100), @viewcount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'v_tb_Vendor_Contacts should contain 0 records after Clear.')
	-- return(1)
end

print 'CleanUp Unit Test'

execute DevOnly_VendorContact_UnitTestCleanUp

print 'END UNIT TEST'

GO


